package examination;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class InvigilatorBean {
    private String staffNo;
    private String name;
    private int workload;
    private String status;
    private String term;
    private String college;

    public String getStaffNo() { return staffNo; }

    public void setStaffNo(String staffNo) {
        this.staffNo = staffNo;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getWorkload() {
        return workload;
    }

    public void setWorkload(int workload) {
        this.workload = workload;
    }

    public String getStatus() { return status; }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getTerm() {
        return term;
    }

    public void setTerm(String term) {
        this.term = term;
    }

    public String getCollege() {
        return college;
    }

    public void setCollege(String college) {
        this.college = college;
    }


    public static InvigilatorBean[] queryAll()
    {
        List<InvigilatorBean> list = new ArrayList<>();

        String query = "SELECT t.teacher_id, teacher_name, teacher_wl, " +
                "work_status, term_id, coll_name " +
                "FROM teacher AS t, proctor_record AS p, college AS c " +
                "WHERE t.teacher_id = p.teacher_id AND t.coll_id = c.coll_id ";

        try
        {
            DatabaseBean dbb = new DatabaseBean();
            ResultSet rs = dbb.executeQuery(query);
            while (rs.next()) {
                String staffNo = rs.getString(1);
                String name = rs.getString(2);
                int workload = rs.getInt(3);
                String status = rs.getString(4);
                String term = rs.getString(5);
                String college = rs.getString(6);

                InvigilatorBean ivb = new InvigilatorBean();
                ivb.setStaffNo(staffNo);
                ivb.setName(name);
                ivb.setWorkload(workload);
                ivb.setStatus(status);
                ivb.setTerm(term);
                ivb.setCollege(college);

                list.add(ivb);
            }
            dbb.close();
            return list.toArray(new InvigilatorBean[0]);
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            return null;
        }
    }

    public static boolean canApply(String examNo)
    {
        String queryTeacherMax = "SELECT exam_status, teacherNeed " +
                "FROM exam WHERE exam_id = '" + examNo + "'";
        String queryTeacherhas = "SELECT COUNT(*) " +
                "FROM proctor WHERE exam_id = '" + examNo + "'";

        try
        {
            DatabaseBean dbb = new DatabaseBean();

            ResultSet rs = dbb.executeQuery(queryTeacherMax);
            rs.next();
            String status = rs.getString(1);
            int teacherNeed = rs.getInt(2);
            if ("审核不通过".equals(status)) return false;

            rs = dbb.executeQuery(queryTeacherhas);
            rs.next();
            int teacherHas = rs.getInt(1);

            dbb.close();
            if (teacherNeed - teacherHas > 0) return true;
            return false;
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            return false;
        }
    }

    public static boolean arrange(String examNo,String staffNo, boolean isMain)
    {
        if (!InvigilatorBean.canApply(examNo)) return false;

        String query = String.format("SELECT isMain FROM proctor WHERE exam_id='%s'", examNo);
        String update = String.format("INSERT INTO proctor (teacher_id, exam_id, isMain) " +
                "VALUES ('%s', '%s', %b)", staffNo, examNo, isMain);
        DatabaseBean dbb = new DatabaseBean();

        try
        {
            ResultSet rs = dbb.executeQuery(query);
            while (rs.next())
            {
                boolean b = rs.getBoolean(1);
                if (b && isMain) return false;
            }

            return dbb.executeUpdate(update);
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            return false;
        }
    }

    public static InvigilatorBean queryByNo(String staffNo)
    {
        List<InvigilatorBean> list = new ArrayList<>();

        String query = "SELECT teacher_name, teacher_wl, work_status, term_id, coll_name " +
                "FROM teacher AS t, proctor_record AS p, college AS c " +
                "WHERE t.teacher_id = p.teacher_id AND t.coll_id = c.coll_id AND t.teacher_id = '" + staffNo + "'";

        try
        {
            DatabaseBean dbb = new DatabaseBean();
            ResultSet rs = dbb.executeQuery(query);
            while (rs.next()) {
                String name = rs.getString(1);
                int workload = rs.getInt(2);
                String status = rs.getString(3);
                String term = rs.getString(4);
                String college = rs.getString(5);

                InvigilatorBean ivb = new InvigilatorBean();
                ivb.setStaffNo(staffNo);
                ivb.setName(name);
                ivb.setWorkload(workload);
                ivb.setStatus(status);
                ivb.setTerm(term);
                ivb.setCollege(college);

                list.add(ivb);
            }
            dbb.close();
            if(list.size()>0) {
            	return list.get(0);
            }
            else {
            	return null;
            }
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            return null;
        }
    }

    public static String[] queryByExamNo(String examNo)
    {
        List<String> list = new ArrayList<>();

        String query = "SELECT teacher_id, isMain FROM proctor " +
                "WHERE exam_id = '" + examNo + "'";

        try
        {
            DatabaseBean db = new DatabaseBean();
            ResultSet rs = db.executeQuery(query);
            while (rs.next()) {
                String teacher_id = rs.getString(1);
                boolean isMain = rs.getBoolean(2);
                if (list.isEmpty() || !isMain)
                    list.add(teacher_id);
                else {
                    String tmp = list.get(0);
                    list.set(0, teacher_id);
                    list.add(tmp);
                }
            }
            db.close();
            return list.toArray(new String[0]);
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            return null;
        }
    }

    public static InvigilatorBean[] queryByNos(String[] staffNoList) {
    	try {
    		List<InvigilatorBean> list = new ArrayList<>();
    		for(String staffNo: staffNoList) {
    			list.add(queryByNo(staffNo));
    		}
    		return list.toArray(new InvigilatorBean[list.size()]);
    	}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
    }
    
    public static void main(String[] args) {
    	boolean success = InvigilatorBean.arrange("159324079280317", "T10002", false);
    	System.out.println(success);
	}
}
